<?php
class ModelCatalogReview extends Model {
	public function addReview($data) {
		global $V5_NOW;
		$data['date_added'] = $V5_NOW;
		
		$this->db->insert('review', $data);
		
		$this->updateRating($data['product_id']);
	}

	public function editReview($review_id, $data) {
		global $V5_NOW;
		$data['date_modified'] = $V5_NOW;
		$this->db->update('review', $data, array('review_id' => $review_id));

		$this->updateRating($data['product_id']);
	}

	public function deleteReview($review_id) {
		$this->db->delete('review', array('review_id' => $review_id));

		$this->updateRating($data['product_id']);
	}

	public function getReview($review_id) {
		return $this->db->queryOne("SELECT DISTINCT *, (SELECT pd.name FROM @@product_description pd
				WHERE pd.product_id = r.product_id AND pd.language_id = " . (int)C('config_language_id') . ") AS product
				FROM @@review r WHERE r.review_id = " . (int)$review_id);
	}

	public function getReviews($filter = null) {
		$sql = "SELECT r.*, pd.name FROM
				@@review r LEFT JOIN @@product_description pd ON (r.product_id = pd.product_id)
				WHERE pd.language_id = " . (int)C('config_language_id');

		if ($filter) {
			if ($filter->get('filter_product_id')) {
				$sql .= " AND r.product_id = " . (int) $filter->get('filter_product_id');
			}
			if ($filter->get('filter_author')) {
				$sql .= " AND r.author = '" . ES($filter->get('filter_author')) . "'";
			}
			if ($filter->get('filter_rating')) {
				$sql .= " AND r.rating = " . (int) $filter->get('filter_rating');
			}
			if ($filter->get('filter_status') !== null) {
				$sql .= " AND r.status = " . (int) $filter->get('filter_status');
			}
			if ($filter->get('filter_date_added')) {
				$sql .= " AND DATE(r.date_added) = '" . $filter->get('filter_date_added') . "'";
			}
			$sql .= $filter->sort('r.date_added', 'DESC');
			$sql .= $filter->limit();
		}
		$this->log->write($sql);
		$query = $this->db->query($sql);

		return $query->rows;
	}

	public function getTotalReviews($filter = null) {
		$sql = "SELECT COUNT(*) AS total FROM @@review r WHERE 1";
		if ($filter) {
			if ($filter->get('filter_product_id')) {
				$sql .= " AND r.product_id = " . (int) $filter->get('filter_product_id');
			}
			if ($filter->get('filter_author')) {
				$sql .= " AND r.author = '" . ES($filter->get('filter_author')) . "'";
			}
			if ($filter->get('filter_rating')) {
				$sql .= " AND r.rating = " . (int) $filter->get('filter_rating');
			}
			if ($filter->get('filter_status') !== null) {
				$sql .= " AND r.status = " . (int) $filter->get('filter_status');
			}
			if ($filter->get('filter_date_added')) {
				$sql .= " AND DATE(r.date_added) = '" . $filter->get('filter_date_added') . "'";
			}
		}
		return $this->db->queryOne($sql);
	}

	public function getTotalReviewsAwaitingApproval() {
		return $this->db->queryOne("SELECT COUNT(*) AS total FROM @@review WHERE status = 0");
	}

	public function updateRating($product_id) {
		$total = $rating = 0;
		$row = $this->db->queryOne("SELECT count(*) as total, ROUND(AVG(rating),1) AS rating FROM @@review
				WHERE status = 1 AND product_id = " . (int)$product_id . " GROUP BY product_id");
		if ($row) {
			$total = $row['total'];
			$rating = $row['rating'];
		}
		$this->db->runSql("UPDATE @@product SET reviews=$total, rating=$rating WHERE product_id=$product_id");
	}
}
?>